// 数据库管理模块（Prisma + SQLite）
import type { Prisma } from '@prisma/client'
import { existsSync } from 'node:fs'
import { copyFile, open as fsOpen, mkdir } from 'node:fs/promises'
import { join } from 'node:path'
import process from 'node:process'
import { app } from 'electron'
// 说明：在真正导入 PrismaClient 之前，需要先设置相关环境变量（DATABASE_URL/引擎路径）
import { getDataPath } from '../config/index'
import { getLogger } from '../logger/index'

let prisma: Prisma.DefaultPrismaClient | null = null

/**
 * 初始化数据库客户端
 *
 * - 开发环境：使用仓库内 app/prisma/data/app.db
 * - 生产环境：使用 %APPDATA%/AutoTools/data/app.db
 *
 * 会在首次运行时确保数据库文件与目录存在，并在生产环境下指明 Prisma 引擎路径。
 */
export async function initializeDatabase(): Promise<Prisma.DefaultPrismaClient> {
  if (prisma)
    return prisma

  const logger = getLogger()
  const _dataPath = getDataPath()

  try {
    // 确保用户数据目录存在（生产环境数据目录）
    await mkdir(join(_dataPath), { recursive: true })
  }
  catch {}

  try {
    // 统一生成 SQLite 绝对路径 URL：file:C:/.../app.db（Prisma/SQLite 可识别）
    // 使用 Electron app.isPackaged 判定环境，避免依赖 NODE_ENV 导致误判
    // 统一开发与生产的数据库路径到 userData/data/app.db，避免多处环境导致导入后界面不可见
    const dbAbsoluteFile = join(_dataPath, 'app.db')
    const devRepoDb = join(process.cwd(), 'prisma', 'data', 'app.db')
    // 若文件不存在则创建空库，避免 SQLite code 14（路径存在但文件缺失）
    try {
      if (!existsSync(dbAbsoluteFile)) {
        // 首次初始化：若检测到仓库内的开发数据库，优先迁移到 userData 目录
        try {
          if (existsSync(devRepoDb)) {
            await copyFile(devRepoDb, dbAbsoluteFile)
          }
          else {
            const fh = await fsOpen(dbAbsoluteFile, 'a')
            await fh.close()
          }
        }
        catch {}
      }
    }
    catch {}
    const dbUrl = `file:${dbAbsoluteFile.replace(/\\/g, '/')}`
    process.env.DATABASE_URL = dbUrl

    // 仅在生产打包环境下指定 Prisma 引擎路径；开发环境交由默认解析
    if (process.env.NODE_ENV === 'production') {
      const unpackedNodeModules = join(process.resourcesPath || process.cwd(), 'app.asar.unpacked', 'node_modules')
      if (process.platform === 'win32') {
        const candidates = [
          join(unpackedNodeModules, '@prisma', 'client', '.prisma', 'client', 'libquery_engine-windows.dll.node'),
          join(unpackedNodeModules, '@prisma', 'client', '.prisma', 'client', 'query_engine-windows.dll.node'),
          join(unpackedNodeModules, '@prisma', 'engines', 'libquery_engine-windows.dll.node'),
          join(unpackedNodeModules, '@prisma', 'engines', 'query_engine-windows.dll.node'),
        ]
        const qe = candidates.find(p => existsSync(p))
        if (qe)
          process.env.PRISMA_QUERY_ENGINE_LIBRARY = qe
        const schemaEngine = join(unpackedNodeModules, '@prisma', 'engines', 'schema-engine-windows.exe')
        if (existsSync(schemaEngine))
          process.env.PRISMA_SCHEMA_ENGINE_BINARY = schemaEngine
      }
    }

    const { PrismaClient } = await import('@prisma/client')
    // 显式指定数据源 URL，避免在打包/开发临时目录解析相对路径导致的无法打开数据库（SQLite 错误码 14）
    prisma = new PrismaClient({
      datasources: {
        db: { url: dbUrl },
      },
    }) as unknown as Prisma.DefaultPrismaClient
    // 启用 WAL 与并发相关 PRAGMA，降低写锁等待与超时
    try {
      await prisma.$executeRawUnsafe('PRAGMA journal_mode=WAL;')
      await prisma.$executeRawUnsafe('PRAGMA synchronous=NORMAL;')
      await prisma.$executeRawUnsafe('PRAGMA busy_timeout=15000;')
      // 可选：内存临时表，减少磁盘 IO
      await prisma.$executeRawUnsafe('PRAGMA temp_store=MEMORY;')
    }
    catch {}

    // 启动时自动补齐缺失列：force_preferred_dates（SQLite 运行库可能来自旧版本）
    try {
      const cols: any[] = await (prisma as any).$queryRawUnsafe('PRAGMA table_info(\'appointment_management\');')
      const hasForce = Array.isArray(cols) && cols.some((r: any) => String(r?.name || '') === 'force_preferred_dates')
      if (!hasForce) {
        try {
          // 在 SQLite 中新增列，默认值为 0（false）
          await (prisma as any).$executeRawUnsafe('ALTER TABLE appointment_management ADD COLUMN force_preferred_dates INTEGER DEFAULT 0;')
          await (prisma as any).$executeRawUnsafe('UPDATE appointment_management SET force_preferred_dates=0 WHERE force_preferred_dates IS NULL;')
          logger.info('✅ 已为 appointment_management 表添加列: force_preferred_dates (default=0)')
        }
        catch (e) {
          logger.warn('⚠️ 运行时添加列 force_preferred_dates 失败（可忽略或稍后手动迁移）:', e)
        }
      }
    }
    catch {}

    // 确保监控任务表存在（避免运行时缺表导致功能失效）
    try {
      await (prisma as any).$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS monitoring_booking_task (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          monitor_id INTEGER NOT NULL,
          appointment_id INTEGER NOT NULL,
          available_date TEXT NOT NULL,
          office_code TEXT NOT NULL,
          service_type TEXT NOT NULL,
          priority INTEGER NOT NULL DEFAULT 1,
          status TEXT NOT NULL DEFAULT 'pending',
          booking_mode TEXT NOT NULL DEFAULT 'request',
          booking_options TEXT,
          error_message TEXT,
          retry_count INTEGER NOT NULL DEFAULT 0,
          max_retries INTEGER NOT NULL DEFAULT 3,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          started_at DATETIME,
          completed_at DATETIME
        );
      `)
      await (prisma as any).$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS idx_mbt_status ON monitoring_booking_task(status);`)
      await (prisma as any).$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS idx_mbt_monitor_id ON monitoring_booking_task(monitor_id);`)
      await (prisma as any).$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS idx_mbt_appointment_id ON monitoring_booking_task(appointment_id);`)
      await (prisma as any).$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS idx_mbt_available_date ON monitoring_booking_task(available_date);`)
      await (prisma as any).$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS idx_mbt_priority ON monitoring_booking_task(priority);`)
      await (prisma as any).$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS idx_mbt_created_at ON monitoring_booking_task(created_at);`)
    }
    catch {}

    logger.info(`Prisma initialized, db = ${dbUrl}; exists=${existsSync(dbAbsoluteFile)}`)
    return prisma
  }
  catch (error) {
    logger.error('数据库初始化失败:', error)
    throw error
  }
}

export function getDatabase(): Prisma.DefaultPrismaClient {
  if (!prisma) {
    throw new Error('数据库尚未初始化，请先调用 initializeDatabase()')
  }
  return prisma
}

export async function closeDatabase(): Promise<void> {
  if (prisma) {
    await prisma.$disconnect()
    prisma = null
    getLogger().info('✅ 数据库连接已关闭')
  }
}
